package controllers;

import java.io.IOException;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.*;

import utils.UsersInfo;

import models.Location;
import models.SolutionAdoption;
import models.SolutionSubmission;

import com.googlecode.objectify.Objectify;
import com.googlecode.objectify.ObjectifyService;
import com.googlecode.objectify.Query;

@SuppressWarnings("serial")
public class ajxExcelReportFinBeneftDetailServlet extends HttpServlet {
	public void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws IOException {
		resp.setContentType("application/vnd.ms-excel"); 		 
	    resp.setHeader("Content-Disposition", "attachment; filename=ExportFinancialDetail.xls"); 
		PrintWriter out = resp.getWriter();


//		out.println("<TABLE><TBODY><TR><TD width=100><SPAN class='lotusBtn lotusBtnAction lotusLeft'><A href='http://opscentral.asia.ad.celestica.com/global/collaboration/SavvySol.nsf/ExportFBDetails?openagent&amp;&amp;FieldList=location~fromDt~toDt=Arden Hills~0~0'>Export XL</A></SPAN></TD></TR></TBODY></TABLE>");		
//		out.println("<P style='FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 14px; FONT-WEIGHT: bold' colspan='5'>Financial Benefit Details - Shared Solutions</P>");
		out.println("<TABLE><TBODY><TR><TD colSpan=12><B>Financial Benefit Details - Shared Solutions</B></TD></TR>");
		
		Date today = new java.util.Date();
		SimpleDateFormat formatNowYear = new SimpleDateFormat("yyyy");
		SimpleDateFormat formatNowMonth = new SimpleDateFormat("MMMMM");
		String tYear = formatNowYear.format( today ); 
		String tMonth = formatNowMonth.format(today );
		
		//tempQuery="location="+tempval1+"&fromDt="+tempval4+"&toDt="+tempval5
		String rlocation = (String) req.getParameter("location");
		String sfromDT = (String) req.getParameter("fromDt");
		String stoDT = (String) req.getParameter("toDt");

		Date fromDT = null;
		Date toDT = null;
		
		if( rlocation!=null && ( rlocation.equals("0")|| rlocation.equals("") ) ){
			rlocation = null;
		}
		
		if( sfromDT!=null && ( sfromDT.equals("0")|| sfromDT.equals("") ) ){
			sfromDT = null;
		}
		if( stoDT!=null && ( stoDT.equals("0")|| stoDT.equals("") ) ){
			stoDT = null;
		}

		
		if( sfromDT != null && !sfromDT.equals("0")){
			fromDT = new Date(sfromDT);
		}
		
		if( stoDT != null && !stoDT.equals("0")){
			toDT = new Date(stoDT);
		}

		Objectify ofy = ObjectifyService.begin();		
		Query<SolutionSubmission> q = null;
		
		if ( rlocation!= null && fromDT != null && toDT != null){ //nothing is null
			q = ofy.query(SolutionSubmission.class).filter("Location", rlocation).filter("submittedON >=", fromDT).filter("submittedON <=", toDT).order("submittedON");
		}else if ( rlocation == null && fromDT != null && toDT != null) { //location is null
			q = ofy.query(SolutionSubmission.class).filter("submittedON >=", fromDT).filter("submittedON <=", toDT).order("submittedON");
		}else if ( rlocation!= null && (fromDT == null || toDT == null) ){ //location is not null and one of the dates field is empty
			q = ofy.query(SolutionSubmission.class).filter("Location", rlocation).order("submittedON");
		}else if ( rlocation == null && fromDT != null && toDT == null ){ //location is empty and one of the date field is NOT empty
			q = ofy.query(SolutionSubmission.class).filter("submittedON >=", fromDT).order("submittedON");
		}else if ( rlocation == null && fromDT == null && toDT != null ){ //location is empty and one of the date field is NOT empty
			q = ofy.query(SolutionSubmission.class).filter("submittedON <=", toDT).order("submittedON");
		}else{
			q = ofy.query(SolutionSubmission.class).filter("sMonth", tMonth).filter("sYear", tYear).order("submittedON");
		}
		
	
		
		int cnt = 1;
		String prevMonth, prevYear;
		prevMonth = prevYear = "";
		
		for ( SolutionSubmission f : q.fetch() ) {
			
			if( cnt == 1){
				prevMonth = f.getsMonth();
				prevYear = f.getsYear();

				out.println("<TABLE><TBODY><TR>");
				out.println("<TD class=style1 colSpan=12 font-size:14px  bgColor=#FF9900><font color=#FFFFFF><b>"+ prevMonth + " " + prevYear+" Submission</b></font></TD>");
				out.println("</TR>");
				
				out.println("<TR bgColor=#99CC00>");
				out.println("<TD class=style2 width='2%'>#</TD>");
				out.println("<TD class=style2 width='5%'>SolutionID </TD>");
				out.println("<TD class=style2 width='5%'>Customer </TD>");
				out.println("<TD class=style2 width='5%'>Process </TD>");
				out.println("<TD class=style2 width='10%'>Title </TD>");
				out.println("<TD class=style2 width='5%'>Posted Date </TD>");
				out.println("<TD class=style2 width='5%'>Date Implemented </TD>");
				out.println("<TD class=style2 width='7.5%'>Annual Savings ($USD)</TD>");
				out.println("<TD class=style2 width='5%'>Hard P&amp;L($USD)</TD>");
				out.println("<TD class=style2 width='5%'>Cost Avoidant ($USD)</TD>");
				out.println("<TD class=style2 width='5%'>Opportunity ($USD)</TD>");
				out.println("<TD class=style2 width='10%'>Submitted By </TD></TR>");					
			}
			
			if( !prevMonth.equals(f.getsMonth()) || !prevYear.equals(f.getsYear())  ){

				prevMonth = f.getsMonth();
				prevYear = f.getsYear();
				
				out.println("<TABLE><TBODY><TR>");
				out.println("<TD class=style1 colSpan=12 font-size:14px bgColor=#FF9900><font color=#FFFFFF><b>"+ prevMonth + " " + prevYear+" Submission</b></font></TD>");
				out.println("</TR>");
				
				out.println("<TR bgColor=#99CC00>");
				out.println("<TD class=style2 width='2%'>#</TD>");
				out.println("<TD class=style2 width='5%'>SolutionID </TD>");
				out.println("<TD class=style2 width='5%'>Customer </TD>");
				out.println("<TD class=style2 width='5%'>Process </TD>");
				out.println("<TD class=style2 width='10%'>Title </TD>");
				out.println("<TD class=style2 width='5%'>Posted Date </TD>");
				out.println("<TD class=style2 width='5%'>Date Implemented </TD>");
				out.println("<TD class=style2 width='7.5%'>Annual Savings ($USD)</TD>");
				out.println("<TD class=style2 width='5%'>Hard P&amp;L($USD)</TD>");
				out.println("<TD class=style2 width='5%'>Cost Avoidant ($USD)</TD>");
				out.println("<TD class=style2 width='5%'>Opportunity ($USD)</TD>");
				out.println("<TD class=style2 width='10%'>Submitted By </TD></TR>");					
				
			}	
			
			out.println("<TR>");
			
			out.println("<TD class=style5 width='2%'>" + cnt + "</TD>");
			out.println("<TD class=style5 width='5%'>" + f.getSolID() + "</TD>");
			out.println("<TD class=style5 width='5%'>" + f.getCustomer() + "</TD>");
			out.println("<TD class=style5 width='5%'>" + f.getProcessArea() + "</TD>");
			out.println("<TD class=style5 width='10%'>" + f.getTitle() + "</TD>");
			out.println("<TD class=style5 width='5%'>" + f.getDtShared() + "</TD>");
			out.println("<TD class=style5 width='5%'>" + f.getDtImplemented() + "</TD>");
			out.println("<TD class=style5 width='7.5%'>" + f.getAnnProdSavings() + "</TD>");
			out.println("<TD class=style5 width='5%'>" + f.getHardSavings() + "</TD>");
			out.println("<TD class=style5 width='5%'>" + f.getCostAvoidance() + "</TD>");
			out.println("<TD class=style5 width='5%'>" + f.getOppurtunityCost() + "</TD>");
			out.println("<TD class=style5 width='10%'>" + UsersInfo.getFullName( f.getSubmittedBY() ) + "</TD>");
			
			out.println("</TR>");
		}			

		
   		out.println("</TBODY></TABLE>");		
	}
}
